package dao.implementations;

import dao.interfaces.EdgeDao;
import dao.interfaces.NodeDao;
import engine.Edge;
import engine.Node;
import java.awt.Color;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * An MS SQL data access object interface for edges.
 * @author Johnny
 */
public class EdgeDaoMsSql extends DaoMsSql implements EdgeDao {

    private NodeDao nodeDao;

    /**
     * Initializes the NodeDao that will be used to retrieve node information.
     * @param nodeDao the NodeDao object that can retrieve node information.
     */
    public EdgeDaoMsSql(NodeDao nodeDao) {
        this.nodeDao = nodeDao;
    }

    /**
     * Returns the list of middle nodes of an edge.
     * @param edgeId the id of the edge containing the nodes.
     * @return the list of middle nodes of an edge.
     */
    private ArrayList<Node> getMiddleNodes(int edgeId) {
        ArrayList<Node> nodeList = new ArrayList<Node>();
        StringBuffer query = new StringBuffer();
        query.append("SELECT N.NodeID, X, Y, Name FROM MiddleNodes AS M, Nodes AS N ");
        query.append("WHERE M.NodeID = N.NodeID AND EdgeID = ");
        query.append(edgeId);
        query.append(" ORDER BY NodeNr");

        try {
            Statement stmt = getStatement();
            ResultSet rs = stmt.executeQuery(query.toString());
            while (rs.next()) {
                nodeList.add(new Node(rs.getInt(1), rs.getFloat(2), rs.getFloat(3),
                        rs.getString(4)));
            }
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(EdgeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }

        return nodeList;
    }

    /**
     * Clears all the middle nodes of an edge.
     * @param edgeId the id of the edge that will be modified.
     */
    private void clearMiddleNodes(int edgeId) {
        String query = "DELETE FROM MiddleNodes WHERE EdgeID = " + edgeId;

        try {
            Statement stmt = getStatement();
            stmt.executeUpdate(query);
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(NodeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public ArrayList<Edge> getEdges() {
        ArrayList<Edge> edgeList = new ArrayList<Edge>();
        String query = "SELECT EdgeID, StartNodeID, StopNodeID, ColorRed, "
                + "ColorGreen, ColorBlue FROM Edges";

        try {
            Statement stmt = getStatement();
            ResultSet rs = stmt.executeQuery(query.toString());
            while (rs.next()) {
                int edgeId = rs.getInt(1);
                Color edgeColor = new Color(rs.getInt(4), rs.getInt(5),
                        rs.getInt(6));
                Node startNode = nodeDao.getNode(rs.getInt(2));
                Node stopNode = nodeDao.getNode(rs.getInt(3));
                edgeList.add(new Edge(edgeId, edgeColor, startNode, stopNode,
                        getMiddleNodes(edgeId)));
            }
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(EdgeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }

        return edgeList;
    }

    public Edge addEdge(int startNodeId, int stopNodeId, int colorRed, int colorGreen, int colorBlue) {
        Edge edge = null;
        StringBuffer sb = new StringBuffer();

        sb.append("SET NOCOUNT ON\n");
        sb.append("INSERT INTO Edges (StartNodeID, StopNodeID, ColorRed, ");
        sb.append("ColorGreen, ColorBlue) ");
        sb.append("VALUES (");
        sb.append(startNodeId);
        sb.append(", ");
        sb.append(stopNodeId);
        sb.append(", ");
        sb.append(colorRed);
        sb.append(", ");
        sb.append(colorGreen);
        sb.append(", ");
        sb.append(colorBlue);
        sb.append(")\n");
        sb.append("SELECT SCOPE_IDENTITY()");

        try {
            Statement stmt = getStatement();
            ResultSet rs = stmt.executeQuery(sb.toString());
            rs.next();

            int newId = rs.getInt(1);
            Node startNode = nodeDao.getNode(startNodeId);
            Node stopNode = nodeDao.getNode(stopNodeId);
            Color edgeColor = new Color(colorRed, colorGreen, colorBlue);
            edge = new Edge(newId, edgeColor, startNode, stopNode);

            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(NodeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }

        return edge;
    }

    public void removeEdge(int edgeId) {
        clearMiddleNodes(edgeId);

        String query = "DELETE FROM Edges WHERE EdgeID = " + edgeId;

        try {
            Statement stmt = getStatement();
            stmt.executeUpdate(query);
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(NodeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void updateEdge(Edge edge) {
        StringBuffer sb = new StringBuffer();
        sb.append("UPDATE Edges SET StartNodeID=");
        sb.append(edge.getStartNode().getId());
        sb.append(", StopNodeID=");
        sb.append(edge.getStopNode().getId());
        sb.append(", ColorRed=");
        sb.append(edge.getColor().getRed());
        sb.append(", ColorGreen=");
        sb.append(edge.getColor().getGreen());
        sb.append(", ColorBlue=");
        sb.append(edge.getColor().getBlue());
        sb.append(" WHERE EdgeID=");
        sb.append(edge.getId());

        try {
            Statement stmt = getStatement();
            stmt.executeUpdate(sb.toString());
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(NodeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void addMiddleNode(int edgeId, int nodeId) {
        int nodeNr = getMiddleNodes(edgeId).size();
        StringBuffer sb = new StringBuffer();
        sb.append("INSERT INTO MiddleNodes (EdgeID, NodeNr, NodeID) ");
        sb.append("VALUES (");
        sb.append(edgeId);
        sb.append(", ");
        sb.append(nodeNr + 1);
        sb.append(", ");
        sb.append(nodeId);
        sb.append(")");

        try {
            Statement stmt = getStatement();
            stmt.executeUpdate(sb.toString());
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(NodeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void removeMiddleNode(int edgeId, int nodeId) {
        // First we ask for the node's position in the middle nodes list.
        int pos = 0;
        ArrayList<Node> middleNodes = getMiddleNodes(edgeId);
        for (int i = 0; i < middleNodes.size(); i++) {
            if (middleNodes.get(i).getId() == nodeId) {
                pos = i + 1;
                break;
            }
        }
        // Next we remove it from the middle nodes list
        StringBuffer query = new StringBuffer();
        query.append("DELETE FROM MiddleNodes WHERE EdgeID = ");
        query.append(edgeId);
        query.append(" AND NodeNr = ");
        query.append(pos);

        try {
            Statement stmt = getStatement();
            stmt.executeUpdate(query.toString());
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(NodeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }
        // Finally we have to pull-up the other nodes in the list.
        query = new StringBuffer();
        query.append("UPDATE MiddleNodes SET NodeNr = NodeNr - 1 WHERE EdgeID = ");
        query.append(edgeId);
        query.append(" AND NodeNr > ");
        query.append(pos);
        try {
            Statement stmt = getStatement();
            stmt.executeUpdate(query.toString());
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(NodeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
